package controller;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import model.CommonValues;
import model.SystemDataManager;
import model.database.DBException;
import model.database.StoreDB;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import controller.SystemController;

public class EmployeeControllerTester {

	@Before
	public void setup() {
		
		System.out.println("in setup");
		// connection to database
		Connection connection = null;
		MysqlDataSource ds = new MysqlConnectionPoolDataSource();
		
    	// set server and database        	
    	ds.setServerName(StoreDB.SERVER_NAME);
    	ds.setDatabaseName(StoreDB.DB_NAME);

		try {
			
			connection = ds.getConnection(StoreDB.DB_USER, StoreDB.DB_PWD);
			Statement statement = connection.createStatement();
			
			// create a customer record
			// create query string
			String query = String.format(
					"INSERT INTO %s (%s, %s, %s, %s, %s) VALUES ('%s', '%s', '%s', '%s', '%s')",
					StoreDB.CUSTOMERS_TABLE, StoreDB.NUMBER, StoreDB.NAME, StoreDB.USER_NAME, StoreDB.ADDRESS, StoreDB.PAYMENT_METHOD,
					TestCommons.TEST_NUMBER, TestCommons.TEST_NAME, TestCommons.TEST_CUSTOMER_USER_NAME, TestCommons.TEST_ADDRESS, TestCommons.TEST_PAYMENT_METHOD);

			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add customer
				fail();
			}
			
			// set user-name for customer
			query = String.format(
					"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
					StoreDB.PASSWORDS_TABLE, StoreDB.USER_NAME, StoreDB.PASSWORD, StoreDB.PERMISSION,
					TestCommons.TEST_CUSTOMER_USER_NAME, TestCommons.TEST_PASSWORD, CommonValues.permissions.CUSTOMER.ordinal());
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add customer user-name
				fail();
			}
			
			// create an employee record
			// create query string
			query = String.format(
					"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
					StoreDB.EMPLOYEES_TABLE, StoreDB.NUMBER, StoreDB.NAME, StoreDB.USER_NAME,
					TestCommons.TEST_NUMBER, TestCommons.TEST_NAME, TestCommons.TEST_EMPLOYEE_USER_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add employee
				fail();
			}
			
			// set user-name for employee
			query = String.format(
					"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
					StoreDB.PASSWORDS_TABLE, StoreDB.USER_NAME, StoreDB.PASSWORD, StoreDB.PERMISSION,
					TestCommons.TEST_EMPLOYEE_USER_NAME, TestCommons.TEST_PASSWORD, CommonValues.permissions.EMPLOYEE.ordinal());

			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add product
				fail();
			}
			
			// create a product record
			// create query string
			query = String.format(
					"INSERT INTO %s (%s, %s) VALUES ('%s', '%s')",
					StoreDB.PRODUCTS_TABLE, StoreDB.NAME, StoreDB.PRICE,
					TestCommons.TEST_PRODUCT_NAME, TestCommons.TEST_PRODUCT_PRICE);

			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add product
				fail();
			}

			// create order record
			// create query string
			query = String.format(
					"INSERT INTO %s (%s, %s, %s, %s, %s, %s) VALUES ('%s', CURDATE(), '%s', '%s', '%s', '%s')",
					StoreDB.ORDERS_TABLE, StoreDB.NUMBER, StoreDB.DATE, StoreDB.ORDERING_CUSTOMER_NUMBER, StoreDB.NAME, 
					StoreDB.AMOUNT, StoreDB.TOTAL_SUM,
					TestCommons.TEST_NUMBER, TestCommons.TEST_NUMBER, TestCommons.TEST_PRODUCT_NAME, TestCommons.TEST_AMOUNT, TestCommons.TEST_AMOUNT*TestCommons.TEST_PRODUCT_PRICE);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not add product
				fail();
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			fail();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	@After
	public void teardown() {
		System.out.println("in teardown");
		// connection to database
		Connection connection = null;
		MysqlDataSource ds = new MysqlConnectionPoolDataSource();

		// set server and database
		ds.setServerName(StoreDB.SERVER_NAME);
		ds.setDatabaseName(StoreDB.DB_NAME);

		try {
			connection = ds.getConnection(StoreDB.DB_USER, StoreDB.DB_PWD);

			Statement statement = connection.createStatement();
			
			// delete test customer record
			String query = String.format("DELETE FROM %s WHERE %s = %s", 
					StoreDB.CUSTOMERS_TABLE, StoreDB.NUMBER, TestCommons.TEST_NUMBER);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete customer
				fail();
			}
			
			// delete test customer's user record
			query = String.format("DELETE FROM %s WHERE %s = '%s'", 
					StoreDB.PASSWORDS_TABLE, StoreDB.USER_NAME, TestCommons.TEST_CUSTOMER_USER_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete customer's user
				fail();
			}
			
			// delete test employee record
			query = String.format("DELETE FROM %s WHERE %s = %s", 
					StoreDB.EMPLOYEES_TABLE, StoreDB.NUMBER, TestCommons.TEST_NUMBER);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete employee
				fail();
			}
			
			// delete test employee's user record
			query = String.format("DELETE FROM %s WHERE %s = '%s'", 
					StoreDB.PASSWORDS_TABLE, StoreDB.USER_NAME, TestCommons.TEST_EMPLOYEE_USER_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete employee's user
				fail();
			}

			// delete test product record
			query = String.format("DELETE FROM %s WHERE %s = '%s' ", 
					StoreDB.PRODUCTS_TABLE, StoreDB.NAME, TestCommons.TEST_PRODUCT_NAME);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete product
				fail();
			}
			
			// delete test order record
			query = String.format("DELETE FROM %s WHERE %s = %s", 
					StoreDB.ORDERS_TABLE, StoreDB.NUMBER, TestCommons.TEST_NUMBER);
			
			// execute query
			if (statement.executeUpdate(query) == 0) {

				// could not delete order
				fail();
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			fail();
		} finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
		System.out.println("done teardown");
	}

	@Test
	public void addCustomer() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add customer
			int newCustomerNum = TestCommons.TEST_NUMBER+1;
			String newCustomerUserName = TestCommons.TEST_USER_NAME + "_new";
			CommonValues.paymentMethods paymentMethod = CommonValues.paymentMethods.values()[TestCommons.TEST_PAYMENT_METHOD]; 
			boolean couldAdd = controller.createCustomerAccount(newCustomerNum, TestCommons.TEST_NAME, newCustomerUserName, TestCommons.TEST_ADDRESS, paymentMethod);
			
			// delete customer (for test-suite maintenance)
			
			// connection to database
			Connection connection = null;
			MysqlDataSource ds = new MysqlConnectionPoolDataSource();

			// set server and database
			ds.setServerName(StoreDB.SERVER_NAME);
			ds.setDatabaseName(StoreDB.DB_NAME);

			try {
				
				connection = ds.getConnection(StoreDB.DB_USER, StoreDB.DB_PWD);
				Statement statement = connection.createStatement();
				
				// query to delete customer record
				String query = String.format("DELETE FROM %s WHERE %s = %s", 
						StoreDB.CUSTOMERS_TABLE, StoreDB.NUMBER, newCustomerNum);
			
				// execute query
				if (statement.executeUpdate(query) == 0) {
	
					// could not delete customer
					fail();
				}
				
				// query to delete customer's user-name record
				query = String.format("DELETE FROM %s WHERE %s = '%s'", 
						StoreDB.PASSWORDS_TABLE, StoreDB.USER_NAME, newCustomerUserName);
			
				// execute query
				if (statement.executeUpdate(query) == 0) {
	
					// could not delete customer
					fail();
				}
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				fail();
			} finally {
				if (connection != null) {
					try {
						connection.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
			
			assertEquals(true, couldAdd);
			
		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}
	
	@Test
	public void addExistingCustomer() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add customer (same as in setup)
			CommonValues.paymentMethods paymentMethod = CommonValues.paymentMethods.values()[TestCommons.TEST_PAYMENT_METHOD]; 
			boolean couldAdd = controller.createCustomerAccount(TestCommons.TEST_NUMBER, TestCommons.TEST_CUSTOMER_USER_NAME, TestCommons.TEST_NAME, TestCommons.TEST_ADDRESS, paymentMethod);
			
			assertEquals(false, couldAdd);
			
		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}

	@Test
	public void addProduct() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add product (same as in setup)
			String newProductName = "new product";
			boolean couldAdd = controller.addNewProduct(newProductName, TestCommons.TEST_PRODUCT_PRICE);

			// delete product (for test-suite maintenance)
			
			// connection to database
			Connection connection = null;
			MysqlDataSource ds = new MysqlConnectionPoolDataSource();

			// set server and database
			ds.setServerName(StoreDB.SERVER_NAME);
			ds.setDatabaseName(StoreDB.DB_NAME);

			try {
				
				connection = ds.getConnection(StoreDB.DB_USER, StoreDB.DB_PWD);
				Statement statement = connection.createStatement();
				
				String query = String.format("DELETE FROM %s WHERE %s = '%s'", 
						StoreDB.PRODUCTS_TABLE, StoreDB.NAME, newProductName);
			
				// execute query
				if (statement.executeUpdate(query) == 0) {
	
					// could not delete order
					fail();
				}
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				fail();
			} finally {
				if (connection != null) {
					try {
						connection.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}

			assertEquals(true, couldAdd);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}

	@Test
	public void addExistingProduct() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add product (same as in setup)
			boolean couldAdd = controller.addNewProduct(TestCommons.TEST_PRODUCT_NAME, TestCommons.TEST_PRODUCT_PRICE);
			
			assertEquals(false, couldAdd);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}
	
	@Test
	public void updateProductPriceTest() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add update product price- PRODUCT EXISTS
			boolean couldUpdate = controller.updateProductPrice(TestCommons.TEST_PRODUCT_NAME, TestCommons.TEST_PRODUCT_PRICE*2);
			
			assertEquals(true, couldUpdate);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}
	
	@Test
	public void updateNonExistingProductPriceTest() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add update product price- PRODUCT DOES NOT EXIST
			boolean couldUpdate = controller.updateProductPrice("blah blah", TestCommons.TEST_PRODUCT_PRICE);
			
			assertEquals(false, couldUpdate);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}

	@Test
	public void getOrders() {
		
		try {
			
			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);
			
			// get all products			
			List<Map<String, String>> result  = controller.getOrders(TestCommons.TEST_NUMBER);
			
			// build expected value
			List<Map<String, String>> expected = new LinkedList<Map<String,String>>();			
			Map<String, String> firstOrder = new HashMap<String, String>();
			firstOrder.put(CommonValues.NUMBER, ""+TestCommons.TEST_NUMBER);
			firstOrder.put(CommonValues.DATE, "2013-05-25");
			firstOrder.put(CommonValues.ORDERING_CUSTOMER_NUMBER, ""+TestCommons.TEST_NUMBER);
			firstOrder.put(CommonValues.NAME, TestCommons.TEST_PRODUCT_NAME);
			firstOrder.put(CommonValues.AMOUNT, ""+TestCommons.TEST_AMOUNT);
			firstOrder.put(CommonValues.TOTAL_SUM, ""+(TestCommons.TEST_AMOUNT*TestCommons.TEST_PRODUCT_PRICE));
			expected.add(firstOrder);
						
			assertEquals(expected, result);
			
		} catch (DBException e) {
			
			e.printStackTrace();
			fail();
		}		
	}

	@Test
	public void updateCustomerDetailsTest() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// update customer details
			boolean couldUpdate = controller.updateCustomerDetails(TestCommons.TEST_NUMBER, "jimmy",
					"10 Rockstar Blv, Rocktown",
					CommonValues.paymentMethods.CHECK);
			assertEquals(true, couldUpdate);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}

	@Test
	public void updateNonExistingCustomerDetailsTest() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// update customer details- CUSTOMER DOES NOT EXIST
			boolean couldUpdate = controller.updateCustomerDetails(TestCommons.TEST_NUMBER+1, "blah",
					"1 blah st, blahville", CommonValues.paymentMethods.CREDIT);
			assertEquals(false, couldUpdate);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}

	@Test
	public void addNewOrder() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add order
			int newOrderNum = TestCommons.TEST_NUMBER+1;
			boolean couldAdd = controller.addNewOrder(newOrderNum, TestCommons.TEST_PRODUCT_NAME, TestCommons.TEST_AMOUNT, TestCommons.TEST_NUMBER);
			
			// delete order (for test-suite maintenance)
			
			// connection to database
			Connection connection = null;
			MysqlDataSource ds = new MysqlConnectionPoolDataSource();

			// set server and database
			ds.setServerName(StoreDB.SERVER_NAME);
			ds.setDatabaseName(StoreDB.DB_NAME);

			try {
				
				connection = ds.getConnection(StoreDB.DB_USER, StoreDB.DB_PWD);

				Statement statement = connection.createStatement();
				String query = String.format("DELETE FROM %s WHERE %s = %s", 
						StoreDB.ORDERS_TABLE, StoreDB.NUMBER, newOrderNum);
			
				// execute query
				if (statement.executeUpdate(query) == 0) {
	
					// could not delete order
					fail();
				}
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				fail();
			} finally {
				if (connection != null) {
					try {
						connection.close();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
			}
			
			assertEquals(true, couldAdd);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}
	
	@Test
	public void addExistingOrder() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// add order (same in setup)
			boolean couldAdd = controller.addNewOrder(TestCommons.TEST_NUMBER, TestCommons.TEST_PRODUCT_NAME, TestCommons.TEST_AMOUNT, TestCommons.TEST_NUMBER);
			
			assertEquals(false, couldAdd);

		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}

	@Test
	public void getProduts() {

		try {

			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);

			// get all products			
			List<Map<String, String>> result  = controller.getProducts();
			
			// build expected value
			List<Map<String, String>> expected = new LinkedList<Map<String,String>>();
			Map<String, String> firstProduct = new HashMap<String, String>();
			firstProduct.put(CommonValues.NAME, TestCommons.TEST_PRODUCT_NAME);
			firstProduct.put(CommonValues.PRICE, ""+TestCommons.TEST_PRODUCT_PRICE);
			expected.add(firstProduct);
			
			assertEquals(expected, result);
			
		} catch (DBException e) {

			e.printStackTrace();
			fail();
		}
	}

	@Test
	public void getProductPrice() {
		
		try {
			
			// create database
			StoreDB db = new StoreDB();
			
			// create model
			SystemDataManager dataManager = new SystemDataManager(db);
			
			// create controller
			SystemController controller = new SystemController(dataManager);
			
			// get test product's price 
			double result  = controller.getProductPrice(TestCommons.TEST_PRODUCT_NAME);
			
			double expected = TestCommons.TEST_PRODUCT_PRICE;
			
			assertEquals(expected, result, 0);
			
		} catch (DBException e) {
			
			e.printStackTrace();
			fail();
		}		
	}
}